package com.sq.partner.manager.dao.impl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.Message;
import com.sq.partner.manager.dao.INlifeCovDao;
import com.sq.partner.manager.mybatis.model.NlifeCov;
import com.sq.partner.manager.util.DBMap;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.excel.ExcelEntity;
import com.sq.partner.manager.util.excel.ExportExcelBuilderTool;

@Repository
public class NlifeCovDaoImpl extends BaseDaoImpl implements INlifeCovDao {
	private static Logger logger = LogUtil.getLog(NlifeCovDaoImpl.class);

	@Override
	public boolean queryPrimaryRiskIsExist(String prodCode) throws Exception {
		int a = (Integer) this.query("queryPrimaryRiskCovCountByProdCode", prodCode);
		if(a>0){
			return true;
		}
		return false;
	}

	@Override
	public Page<NlifeCov> queryCovPageByCriteria(Page<NlifeCov> page) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		//查分页
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			NlifeCov nc = page.getType();
			String sql = "SELECT T1.CORE_RISK_CODE as coreRiskCode,DECODE(T2.CORE_COV_LEVEL,'2','└'||T1.CORE_COV_CODE,"
					+ "T1.CORE_COV_CODE) AS coreCovCode, DECODE(T2.CORE_COV_LEVEL,'2','└' || T2.CORE_COV_NAME,"
					+ " T2.CORE_COV_NAME) AS covName, DECODE(T2.CORE_COV_LEVEL, '1', T2.CORE_COV_LEVEL || ',' || T1.CORE_COV_CODE,'2',"
					+ "  T2.CORE_COV_LEVEL || ',' || T1.PARENT_COV_CODE) AS TRADPRODCODE, T1.PARENT_COV_CODE AS parentCov, t2.core_cov_level AS coreCovLevel,T4.COV_AMT as covAmt,"
					+ "T1.CORE_COV_ALIAS as coreCovAlias,"
					+ "T4.IS_INPUT_AMOUNT as isInputAmount,T4.NO_CALIM_AMOUNT as noCalimAmount,T4.CLAIM_RATE* 100 as claimRate,T4.CAL_ID as calId, T4.Amt_Limit as  amtLimit,T4.Amt_Rate as amtRate,T4.AMT_TYPE AS amtType,T1.PARENT_COV_CODE || T1.CORE_COV_CODE AS ORDER_COL"
					+ " ,T4.SHORTRATE_FLAG as shortrateFlag"
					+ " FROM T_NLIFE_CORE_RISK_COV T1 INNER JOIN T_NLIFE_CORE_COV T2 ON T2.CORE_COV_CODE = T1.CORE_COV_CODE"
					+ " LEFT JOIN T_NLIFE_CORE_COV T3  ON T1.PARENT_COV_CODE = T3.CORE_COV_CODE LEFT JOIN T_NLIFE_COV T4"
					+ " ON T1.CORE_RISK_CODE = T4.CORE_RISK_CODE AND T1.CORE_COV_CODE = T4.CORE_COV_CODE AND T4.PROD_CODE = ?  AND T4.RATION_ID = ?"
					+ " WHERE T1.CORE_RISK_CODE = ? ORDER BY ORDER_COL ASC";
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, nc.getProdCode());
			pstmt.setObject(2, nc.getRationId());
			pstmt.setObject(3, nc.getCoreRiskCode());
			rs = pstmt.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
			String sql1  = "SELECT T.CAL_ID AS CALID, T.CAL_ID_NAME AS CALNAME,T.SHOW_LEVEL  AS SHOWLEVEL FROM T_TRAD_ITEM_CAL T WHERE T.CAL_DEF_ID = '0000000008' AND T.SHOW_LEVEL>=0 ORDER BY T.CAL_ID ASC";
			pstmt1 = conn.prepareStatement(sql1);
			rs = pstmt1.executeQuery();
			List  ls = DBUtil.transRs2List(rs);
			page.setObj(ls);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public boolean saveCovInfo(NlifeCov nc) throws Exception {
		return this.add("insertNlifeCov", nc);
	}

	@Override
	public boolean deleteCov(NlifeCov cov) throws Exception {
		return this.delete("delNlifeCovById", cov);
	}

	@Override
	public boolean deleteCovInfoByProdCode(String prodCode) throws Exception {
		return this.delete("delNlifeCovByProdCode", prodCode);
	}

	@Override
	public List<NlifeCov> queryItemsAndCovsInfoByPolicyId(String policyId) throws Exception {
		return this.queryList("queryCovsInfoByPolicyId", policyId);
	}

	@Override
	public Page<NlifeCov> queryCovPageByCriteria1(Page<NlifeCov> page)
			throws Exception {

		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		//查分页
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			NlifeCov nc = page.getType();
			String sql = "SELECT T1.CORE_RISK_CODE as coreRiskCode,DECODE(T2.CORE_COV_LEVEL,'2',T1.CORE_COV_CODE,"
					+ "T1.CORE_COV_CODE) AS coreCovCode, DECODE(T2.CORE_COV_LEVEL,'2',T2.CORE_COV_NAME,"
					+ " T2.CORE_COV_NAME) AS covName, DECODE(T2.CORE_COV_LEVEL, '1', T2.CORE_COV_LEVEL || ',' || T1.CORE_COV_CODE,'2',"
					+ "  T2.CORE_COV_LEVEL || ',' || T1.PARENT_COV_CODE) AS TRADPRODCODE, T1.PARENT_COV_CODE AS parentCov, t2.core_cov_level AS coreCovLevel,T4.COV_AMT as covAmt,"
					+ "T4.IS_INPUT_AMOUNT as isInputAmount,T4.NO_CALIM_AMOUNT as noCalimAmount,T4.CLAIM_RATE* 100 as claimRate,T4.Amt_Limit as  amtLimit,T4.Amt_Rate as amtRate,T4.CAL_ID as calId,T1.PARENT_COV_CODE || T1.CORE_COV_CODE AS ORDER_COL"
					+ " FROM T_NLIFE_CORE_RISK_COV T1 INNER JOIN T_NLIFE_CORE_COV T2 ON T2.CORE_COV_CODE = T1.CORE_COV_CODE"
					+ " LEFT JOIN T_NLIFE_CORE_COV T3  ON T1.PARENT_COV_CODE = T3.CORE_COV_CODE LEFT JOIN T_NLIFE_COV T4"
					+ " ON T1.CORE_RISK_CODE = T4.CORE_RISK_CODE AND T1.CORE_COV_CODE = T4.CORE_COV_CODE AND T4.PROD_CODE = ? AND T4.RATION_ID = ?"
					+ " WHERE T1.CORE_RISK_CODE = ?  AND T4.CAL_ID is not null ORDER BY ORDER_COL ASC";
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, nc.getProdCode());
			pstmt.setObject(2, nc.getRationId());
			pstmt.setObject(3, nc.getCoreRiskCode());
			rs = pstmt.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
			String sql1  = "SELECT T.CAL_ID AS CALID, T.CAL_ID_NAME AS CALNAME FROM T_TRAD_ITEM_CAL T WHERE T.CAL_DEF_ID = '0000000008' ORDER BY T.CAL_ID ASC";
			pstmt1 = conn.prepareStatement(sql1);
			rs = pstmt1.executeQuery();
			List  ls = DBUtil.transRs2List(rs);
			page.setObj(ls);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}
	@Override
	public boolean queryPrimaryRiskIsExist1(String prodCode) throws Exception {
		int a = (Integer) this.query("queryPrimaryRiskCovCountByProdCode1", prodCode);
		if(a>0){
			return true;
		}
		return false;
	}

	@Override
	public boolean queryPrimaryRiskIsExist2(Map<String, String> map)
			throws Exception {
		int a = (Integer) this.query("queryPrimaryRiskCovCountByProdCode2", map);
		if(a>0){
			return true;
		}
		return false;
	}

	@Override
	public Integer queryPrimaryRiskIsExist3(String prodCode) throws Exception {
		// TODO Auto-generated method stub
		return  (Integer) this.query("queryPrimaryRiskCovCountByProdCode3", prodCode);
	}

	@Override
	public List<Map<String, Object>> impNlifeCov(Map<String, Object> map)
			throws SQLException {
		// TODO Auto-generated method stub
		return this.queryList("impNlifeCovByMap", map);
	}

	@Override
	public String downloadCovExcel(String prodCode, HttpServletResponse resp)
			throws Exception {
		// 先由prodCode查条款信息
		String sql1 = "select comm.prod_code," + "cov.core_risk_code,"
				+ "risk.risk_name, " + "cov.core_cov_code," + " cov.cov_name,"
				+ "cal.cal_id_name," + "comm.prem_rate ," + "comm.cond1 ,"
				+ "comm.cond2 " + ",comm.cond7 "
				+ "from t_nlife_prod prod , "
				+ "t_nlife_risk risk, "
				+ "t_nlife_cov  cov, "
				+ "t_trad_fee_cov_common comm ," + "t_trad_item_cal cal "
				+ "where prod.prod_code = risk.prod_code "
				+ "and risk.prod_code = cov.prod_code "
				+ "and cov.core_risk_code=risk.core_risk_code "
				+ "and cal.cal_id = cov.cal_id "
				+ "and comm.prod_code=cov.prod_code "
				+ "and comm.risk_code=risk.core_risk_code "
				+ "and comm.cov_code=cov.core_cov_code "
				+ "and prod.prod_code = ? " + "and cov.cal_id is not null "
				+ "order By  cond1,cov_code";
		// 創建connection
		Connection conn = null;
		try {
			conn = this.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql1);
			pstmt.setObject(1, prodCode);
			logger.debug("执行SQL：" + sql1);
			ResultSet rs = pstmt.executeQuery();
			List<Map<String, Object>> list = transRs2Listmap(rs);

			ExcelEntity excel = new ExcelEntity();

			String commonTitles = "产品代码,条款代码,条款名称,责任代码,责任名称,责任保费算法";

			excel.setFileName(prodCode + "算法导出");
			String titleKeys = "prod_code,core_risk_code,risk_name,core_cov_code,cov_name,cal_id_name";
			String clomnName = "cal_id_name";
			String columns = "15,10,50,10,20,25";
			// 责任固定保费算法
			if (list.get(0).get(clomnName).equals("责任固定保费算法")
					|| list.get(0).get(clomnName).equals("责任固定保费算法-共享保费")) {
				String titles = commonTitles + ",固定保费";
				titleKeys += ",prem_rate";
				columns += ",15";
				excel.setColumns(columns);
				excel.setResults(list);
				excel.setTitles(titles);
				excel.setTitleKeys(titleKeys);
				ExportExcelBuilderTool builder = new ExportExcelBuilderTool();
				builder.buildExcel(resp, excel);
			} else if (list.get(0).get(clomnName).equals("责任按天算法")
					|| list.get(0).get(clomnName).equals("责任按月算法")
					|| list.get(0).get(clomnName).equals("责任按年算法")) {

				// list转化为合适的格式
				List<Map<String, Object>> rsList = changeList2Template1(list);

				// keys附加上字段
				String s = findKeys1(list);
				titleKeys += s;
				// 标题添加
				String[] titles = s.split(",");
				if (list.get(0).get(clomnName).equals("责任按天算法")) {
					for (int i = 1; i < titles.length; i++) {
						commonTitles += "," + titles[i] + "天保费";
						columns += ",10";
					}
				} else if (list.get(0).get(clomnName).equals("责任按月算法")) {
					for (int i = 1; i < titles.length; i++) {
						commonTitles += "," + titles[i] + "月保费";
						columns += ",10";
					}
				} else {
					for (int i = 1; i < titles.length; i++) {
						commonTitles += "," + titles[i] + "年保费";
						columns += ",10";
					}
				}
				excel.setColumns(columns);
				excel.setResults(rsList);
				excel.setTitles(commonTitles);
				excel.setTitleKeys(titleKeys);
				ExportExcelBuilderTool builder = new ExportExcelBuilderTool();
				builder.buildExcel(resp, excel);
			} else if (list.get(0).get(clomnName).equals("责任按天区间算法")
					|| list.get(0).get(clomnName).equals("驾乘险座位区间算法")) {			
				if (list.get(0).get(clomnName).equals("责任按天区间算法")) {
					List<Map<String, Object>> rsList = changeList2Template2(list);
					String s = findKeys2(list);
					String[] titles = s.split(",");
					titleKeys += s;
					for (int i = 1; i < titles.length; i++) {
						commonTitles += "," + titles[i].replace("to", "至")
								+ "天";
						columns += ",10";

					}
					excel.setColumns(columns);
					excel.setResults(rsList);
					excel.setTitles(commonTitles);
					excel.setTitleKeys(titleKeys);
					ExportExcelBuilderTool builder = new ExportExcelBuilderTool();
					builder.buildExcel(resp, excel);
				} else {
					List<Map<String, Object>> rsList = changeList2Template3(list);
					String s = findKeys2(list);
					String[] titles = s.split(",");
					for (Map<String, Object> map : rsList) {
						
					
						
						switch (Integer.parseInt((String) map.get("cond7"))) {
						case 1:
							map.put("carKind", "非营业客车");
							break;
						case 2:
							map.put("carKind", "营业客车");
							break;
						case 3:
							map.put("carKind", "非营业货车");
							break;
						case 4:
							map.put("carKind", "营业货车");
							break;
						default:
							break;
						}
					}
					titleKeys += ",carKind" + s;
					commonTitles += ",车辆性质";
					columns += ",10";

					for (int i = 1; i < titles.length; i++) {
						commonTitles += "," + titles[i].replace("to", "至")
								+ "座";
						columns += ",10";
					}
					logger.debug(commonTitles);
					excel.setColumns(columns);
					excel.setResults(rsList);
					excel.setTitles(commonTitles);
					excel.setTitleKeys(titleKeys);
					ExportExcelBuilderTool builder = new ExportExcelBuilderTool();
					builder.buildExcel(resp, excel);

				}
				
			} else {
				logger.debug("失败");
				return "失败 原因:不支持的导出类型";
			}

		} finally {
			close(conn);
		}
		return "导出成功";
	}

	// 将list转化为 责任按天格式
	public List<Map<String, Object>> changeList2Template1(
			List<Map<String, Object>> list) {
		List<Map<String, Object>> rslist = new ArrayList<Map<String, Object>>();
		List<String> allProdCov = new ArrayList<String>();
		for (Map p : list) {
			// 选择特征码
			allProdCov.add((String) p.get("core_cov_code"));
		}
		// 去重
		for (int i = 0; i < allProdCov.size() - 1; i++) {
			for (int j = allProdCov.size() - 1; j > i; j--) {
				if (allProdCov.get(j).equals(allProdCov.get(i))) {
					allProdCov.remove(j);
				}
			}
		}

		int allCov = allProdCov.size();

		for (int i = 0; i < (list.size() / allCov); i++) {
			for (int j = 0; j < allCov; j++) {
				list.get(j).put(
						list.get(j + allCov * i).get("cond1").toString(),
						list.get(j + allCov * i).get("prem_rate"));
			}
		}
		for (int i = 0; i < allCov; i++) {
			rslist.add(list.get(i));
		}
		return rslist;
	}

	// 将list转化为 责任按区间格式
	public List<Map<String, Object>> changeList2Template2(
			List<Map<String, Object>> list) {
		List<Map<String, Object>> rslist = new ArrayList<Map<String, Object>>();
		List<String> allProdCov = new ArrayList<String>();
		for (Map p : list) {
			// 选择特征码
			allProdCov.add((String) p.get("core_cov_code"));
		}
		// 去重
		for (int i = 0; i < allProdCov.size() - 1; i++) {
			for (int j = allProdCov.size() - 1; j > i; j--) {
				if (allProdCov.get(j).equals(allProdCov.get(i))) {
					allProdCov.remove(j);
				}
			}
		}

		int allCov = allProdCov.size();

		for (int i = 0; i < (list.size() / allCov); i++) {
			for (int j = 0; j < allCov; j++) {
				list.get(j).put(
						list.get(j + allCov * i).get("cond1").toString()
								+ "to"
								+ list.get(j + allCov * i).get("cond2")
										.toString(),
						list.get(j + allCov * i).get("prem_rate"));
			}
		}
		for (int i = 0; i < allCov; i++) {
			rslist.add(list.get(i));
		}
		return rslist;
	}

	// 将list转化为 按座位区间的
	public List<Map<String, Object>> changeList2Template3(
			List<Map<String, Object>> list) {
		List<Map<String, Object>> rslist = new ArrayList<Map<String, Object>>();
		List<String> allProdCov = new ArrayList<String>();
		for (Map p : list) {
			// 选择特征码
			allProdCov.add((String) p.get("core_cov_code") + p.get("cond7"));
		}
		// 去重
		for (int i = 0; i < allProdCov.size() - 1; i++) {
			for (int j = allProdCov.size() - 1; j > i; j--) {
				if (allProdCov.get(j).equals(allProdCov.get(i))) {
					allProdCov.remove(j);
				}
			}
		}

		int allCov = allProdCov.size();

		for (int i = 0; i < (list.size() / allCov); i++) {
			for (int j = 0; j < allCov; j++) {
				list.get(j).put(
						list.get(j + allCov * i).get("cond1").toString()
								+ "to"
								+ list.get(j + allCov * i).get("cond2")
										.toString(),
						list.get(j + allCov * i).get("prem_rate"));
			}
		}
		for (int i = 0; i < allCov; i++) {
			rslist.add(list.get(i));
		}
		return rslist;
	}

	// 在list中间找Key的方法(责任按天)
	public String findKeys1(List<Map<String, Object>> list) {

		List<String> indexList = new ArrayList<String>();
		for (Map map : list) {
			indexList.add(map.get("cond1").toString());
		}
		// 去重
		for (int i = 0; i < indexList.size() - 1; i++) {
			for (int j = indexList.size() - 1; j > i; j--) {
				if (indexList.get(j).equals(indexList.get(i))) {
					indexList.remove(j);
				}
			}
		}
		String result = "";
		for (String s : indexList) {
			result += "," + s;
		}
		return result;
	}

	// 责任天区间找keys
	public String findKeys2(List<Map<String, Object>> list) {

		List<String> indexList = new ArrayList<String>();
		for (Map map : list) {
			indexList
					.add(map.get("cond1").toString() + "to" + map.get("cond2"));
		}
		// 去重
		for (int i = 0; i < indexList.size() - 1; i++) {
			for (int j = indexList.size() - 1; j > i; j--) {
				if (indexList.get(j).equals(indexList.get(i))) {
					indexList.remove(j);
				}
			}
		}
		String result = "";
		for (String s : indexList) {
			result += "," + s;
		}
		return result;
	}

	/**
	 * 将resultSet结果放到List<Map>里，结果不区分大小写
	 * @param rs rs不要调用rs.next
	 * @return List<Map> ，没有结果返回null 
	 * @throws SQLException
	 */
	public static List<Map<String,Object>> transRs2Listmap(ResultSet rs) throws SQLException {
        List re = null;
        try {
            ResultSetMetaData resultMeta = rs.getMetaData();
            DBMap to = null;
            if (rs.next()){
                re = new ArrayList();
                do{
                    to = new DBMap();
                    for (int j = 1; j <= resultMeta.getColumnCount(); j++) {
                    	to.put( resultMeta.getColumnName(j),rs.getObject(j));
                    }
                    re.add(to);
                }while(rs.next());
            }
        } catch (Exception e) {
            throw new SQLException("Load to DBMap failed");
        }
        return re;
    }

	public List<NlifeCov> queryNlifeCovByMap(Map<String, Object> map) throws SQLException{
		return this.queryList("queryNlifeCovByMap", map);
	}
}
